package cn.edu.scau.cmi.tangqingning.tools;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import com.mysql.jdbc.Statement;

/**
 * DbConnection是一个和数据库连接的类，该类使用了JDBC技术，通过配置db.properties，
 * 填写完数据库驱动，url，用户名和密码即可连接到数据库。值得注意的是db.properties
 * 文件所在项目的路径，同时还要检查上述的配置是否正确，否则会发生错误。
 * @author 庄桂满
 *
 */
public class DbConnection {

	private static String username = null;
	private static String password = null;
	private static String driver = null;
	private static String url = null;
	ResultSet res = null;
	static {
		Properties ps = new Properties();
		try {
			ps.load(DbConnection.class.getResourceAsStream("/db.properties"));
			driver = ps.getProperty("driver");
			url = ps.getProperty("url");
			username = ps.getProperty("username");
			password = ps.getProperty("password");
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 连接数据库
	 * 该方法通过数据库驱动、连接数据库所需要的url、用户名和密码来连接数据库。
	 * @param createBrandDatabase String，如果flag为true则创表
	 * @param flag boolean，判断是否需要执行创表步骤
	 * @return connection
	 */
	public static Connection getConnection(String createBrandDatabase, boolean flag) {
		Connection connection = null;
		try {
			Class.forName(driver);
			connection = DriverManager.getConnection(url, username, password);
			// 创表
			if (flag) {
				String checkTable;
				if (createBrandDatabase.charAt(13) == 'p') {
					checkTable = "show tables like \"projectdata\"";
				} else {
					checkTable = "show tables like \"stuperinfo\"";
				}

				Statement stmt = (Statement) connection.createStatement();

				ResultSet resultSet = stmt.executeQuery(checkTable);
				if (resultSet.next()) {
					System.out.println("table exist!");
				} else {
					if (stmt.executeUpdate(createBrandDatabase) == 0)
						System.out.println("create table success!");
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return connection;
	}

	/**
	 * 关闭数据库
	 * 如果connection不为空，则关闭数据库，否则抛出异常
	 * @param connection Connection
	 */
	public static void closeConnection(Connection connection) {
		// TODO Auto-generated method stub
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
		}
	}

	/**
	 * 查询数据库
	 * 把excel表格导入数据库时使用此方法
	 * @param sql String
	 * @param str[] String
	 * @return res ResultSet
	 */
	public ResultSet Search(String sql, String str[]) {
		Connection connection = getConnection("", false);
		try {
			PreparedStatement pst = connection.prepareStatement(sql);
			if (str != null) {
				for (int i = 0; i < str.length; i++) {
					pst.setString(i + 1, str[i]);
				}
			}
			res = pst.executeQuery();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return res;
	}

	/**
	 * 增删修改数据库
	 * 把excel表格导入数据库时使用此方法
	 * @param sql String
	 * @param str[] String
	 * @return a 0或1，代表是否修改成功
	 */
	public int addDataToDb(String sql, String str[]) {
		int a = 0;
		Connection connection = getConnection("", false);
		try {
			PreparedStatement pst = connection.prepareStatement(sql);
			if (str != null) {
				for (int i = 0; i < str.length; i++) {
					pst.setString(i + 1, str[i]);
					System.out.println(str[i] + "  ---导入成功");
				}
			}
			a = pst.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return a;
	}

}
